﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace CM_Classes
{
    public class Group
    {
        private int groupID;
        private string groupName;
        private int groupPriority;
        private string groupDescription;
        private bool groupDisabled;
        private string subGroupOf;

        public int GroupID
        {
            get { return groupID; }
            set { groupID = value; }
        }
        public string GroupName
        {
            get { return groupName; }
            set { groupName = value; }
        }
        
        public int GroupPriority 
         {
            get { return groupPriority; }
            set { groupPriority = value; }
        }
        
        public string GroupDescription 
        {
            get { return groupDescription; }
            set { groupDescription = value; }
        }
        public bool GroupDisabled 
        {
            get { return groupDisabled; }
            set { groupDisabled = value; }
        }

        public string SubGroupOf
        {
            get { return subGroupOf; }
            set { subGroupOf = value; }
        }


        public static string AddGroup(string gn, int gp, string gd, bool gdis, string sgo)
        {
            String connectionString = "Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";
            SqlConnection connection = new SqlConnection(connectionString);

            string result = "";
            try
            {
                using (SqlCommand InsertCommand = new SqlCommand("INSERT INTO [ConfigurationManager].[dbo].[Group] VALUES(@GroupName, @GroupPriority, @GroupDescription, @GroupDisabled, @SubGroupOf)", connection))
                {
                    InsertCommand.Parameters.AddWithValue("@GroupName", gn);
                    InsertCommand.Parameters.AddWithValue("@GroupPriority", gp);
                    InsertCommand.Parameters.AddWithValue("@GroupDescription", gd);
                    InsertCommand.Parameters.AddWithValue("@GroupDisabled", gdis);
                    InsertCommand.Parameters.AddWithValue("@SubGroupOf", sgo);

                    connection.Open();
                    InsertCommand.ExecuteNonQuery();
                    result = gn;
                }
            }
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }
            return result;
        }


        public static string SaveGroup(string gn, int gp, string gd, bool gdis, string sgo)
        {
            String connectionString = ConfigurationManager.ConnectionStrings["Group"].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);

            string result = "";

            try
            {
                using (SqlCommand UpdateCommand = new SqlCommand(
                "UPDATE [ConfigurationManager].[dbo].[Group] SET  GroupName=@GroupName, GroupPriority = @GroupPriority, GroupDescription = @GroupDescription, GroupDisabled = @GroupDisabled, SubGroupOf=@SubGroupOf where groupName ='" + gn + "'", connection))
                {
                    UpdateCommand.Parameters.AddWithValue("@GroupName", gn);
                    UpdateCommand.Parameters.AddWithValue("@GroupPriority", gp);
                    UpdateCommand.Parameters.AddWithValue("@GroupDescription", gd);
                    UpdateCommand.Parameters.AddWithValue("@GroupDisabled", gdis);
                    UpdateCommand.Parameters.AddWithValue("@SubGroupOf", gdis);

                    connection.Open();
                    UpdateCommand.ExecuteNonQuery();
                    result = gn;
                }
            }
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }
            return result;
        }


        public static Group SearchGroupByName(string gn)
        {
            String connectionString = ConfigurationManager.ConnectionStrings["Group"].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);

            string result = "";
            Group g = new Group();

            try
            {
                string select = "SELECT * FROM [ConfigurationManager].[dbo].[Group] WHERE GroupName ='" + gn + "'";
                connection.Open();
                SqlCommand SearchCommand = new SqlCommand(select, connection);
                SqlDataReader reader = SearchCommand.ExecuteReader();
                while (reader.Read())
                {
                    g.GroupID = Convert.ToInt32(reader[0]);
                    g.GroupName = reader[1].ToString();
                    g.GroupPriority = Convert.ToInt32(reader[2]);
                    g.GroupDescription = reader[3].ToString();
                    g.SubGroupOf = reader[4].ToString();
                }
                reader.Close();
                connection.Close();
                return g;

            }
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }
            g.GroupName = result;
            return g;
        }


        public static string DeleteGroupById(int gid)
        {
            String connectionString = ConfigurationManager.ConnectionStrings["Group"].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);

            string result = "";

            try
            {
                using (SqlCommand DeleteCommand = new SqlCommand(
                "DELETE FROM [ConfigurationManager].[dbo].[Group] HWERE GroupId ='" + gid + "'", connection))
                {
                    connection.Open();
                    DeleteCommand.ExecuteNonQuery();
                    result = gid.ToString();
                }
            }
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }
            return result;
        }


        public static string IsSubGroupOf(string gid)
        {
            String connectionString = ConfigurationManager.ConnectionStrings["Group"].ConnectionString;
            SqlConnection connection = new SqlConnection(connectionString);

            string result = "";

            try
            {
                using (SqlCommand SearchCommand = new SqlCommand(
                "SELECT * [ConfigurationManager].[dbo].[Group] where groupId ='" + gid + "'", connection))
                {
                    connection.Open();
                    SearchCommand.ExecuteNonQuery();
                    result = "@SubGroupOf";
                }
            }
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }
            return result;
        }

        public List<Group> GetAllGroup()
        {
            List<Group> GroupList = new List<Group>();
            String connectionString = "Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string queryString = "Select groupID, groupName, groupPriority, groupDescription, groupdisabled, subGroupOf  from  [ConfigurationManager].[dbo].[Group]";

                try
                {

                    connection.Open();

                    SqlCommand command = new SqlCommand(queryString, connection);

                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        Group group = new Group();

                        group.groupID = reader.GetInt32(0);
                        group.groupName = reader.GetString(1);
                        group.groupPriority = reader.GetInt32(2);
                        group.groupDescription = reader.GetString(3);
                        group.groupDisabled = reader.GetBoolean(4);
                        group.subGroupOf = reader.GetString(5);
                      

        

                        GroupList.Add(group);
                    }

                }

                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }

                return GroupList;
            }
        }

        public string SaveGroup()
        {
            throw new NotImplementedException();
        }
    }
}
